// tablePairs.js
const path = require('path');
const { getImageSize } = require('../../utils/imageUtils');

// 定义数据集的数据列表
class PairsTable {
    constructor(app, db) {
        this.app = app;
        this.db = db;
    }

    init() {
        // 创建dataset list表结构
        const sqlDataListTable = `
        CREATE TABLE IF NOT EXISTS pairs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            image_path TEXT NOT NULL,
            label_path TEXT NOT NULL,
            subset_type TEXT DEFAULT '',
            create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );`;

        this.db.run(sqlDataListTable, (err) => {
            if (err) {
                console.error('Create "pairs" Table Failed, Error: ', err);
            }
        });
    }

    async insertPairs(pairs) {
        return new Promise((resolve, reject) => {
            this.db.serialize(() => {
                this.db.run('BEGIN TRANSACTION;'); // 开始事务
                try {
                    pairs.forEach((pair) => {
                        this.db.run(
                            `INSERT INTO pairs (image_path, label_path, create_time, update_time) VALUES (?, ?, datetime(CURRENT_TIMESTAMP, '+8 hour') ,datetime(CURRENT_TIMESTAMP, '+8 hour'));`,
                            [pair.image_path, pair.label_path]
                        );
                    });

                    this.db.run('COMMIT;');
                    resolve(true);
                } catch (err) {
                    this.db.run('ROLLBACK;');
                    reject(err.message);
                }
            });
        });
    }

    async deletePairs(pairIds) {
        // 检查 pairIds 是否为空
        if (!pairIds || pairIds.length === 0) {
            throw new Error('未提供有效的 pairIds');
        }

        // 构建 SQL 语句，使用 IN 子句批量删除
        const deleteStatement = `
            DELETE FROM pairs WHERE id IN (${pairIds.map(() => '?').join(',')});
        `;

        return new Promise((resolve, reject) => {
            this.db.run(deleteStatement, pairIds, (err) => {
                if (err) {
                    reject(err.message);
                } else {
                    resolve(true);
                }
            });
        });
    }

    async updateLabelPath(pairId, newLabelPath) {
        let updateStatement = 'UPDATE pairs SET';
        const params = [];

        if (newLabelPath) {
            updateStatement += ' label_path = ?,';
            params.push(newLabelPath);
        }

        // Remove the trailing comma and add the WHERE clause
        updateStatement += " update_time = datetime(CURRENT_TIMESTAMP, '+8 hour') WHERE id = ?;";
        params.push(pairId);

        return new Promise((resolve, reject) => {
            this.db.run(updateStatement, params, (err) => {
                if (err) {
                    reject(err.message);
                } else {
                    resolve(true);
                }
            });
        });
    }

    // 完整获取所有的列表数据
    async getPairs() {
        const selectStatement = `
            SELECT * FROM pairs;
        `;

        return new Promise((resolve, reject) => {
            this.db.all(selectStatement, (err, rows) => {
                if (err) {
                    reject(err.message);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    // 分页获取列表数据
    async getPairsPageWithTotal(datasetDir, page, pageSize, load_details = false) {
        return new Promise(async (resolve, reject) => {
            // 查询paris信息，包括总量以及当前页
            const countSql = `SELECT COUNT(*) AS count FROM pairs;`;
            this.db.get(countSql, (err, result) => {
                if (err) {
                    reject(new Error('Select Count Failed: ' + err.message));
                } else {
                    const totalRecords = result.count;
                    const totalPages = Math.ceil(totalRecords / pageSize);
                    const offset = (page - 1) * pageSize;
                    const dataSql = `
                        SELECT * FROM pairs
                        ORDER BY id ASC
                        LIMIT ? OFFSET ?;`;
                    this.db.all(dataSql, [pageSize, offset], (err, rows) => {
                        if (err) {
                            reject(new Error('Select Page Failed: ' + err.message));
                        } else {
                            let pairsList = rows.map((pair) => ({
                                pairs_id: pair.id,
                                image_path: path.join(datasetDir, pair.image_path),
                                label_path: path.join(datasetDir, pair.label_path),
                                create_time: pair.create_time,
                                update_time: pair.update_time
                            }));

                            if (load_details) {
                                for (let i = 0; i < pairsList.length; i++) {
                                    let pair = pairsList[i];
                                    pair.image_info = getImageSize(pair.image_path);
                                    // console.log('pair.image_info = ', pair.image_info);
                                }
                            }

                            resolve({
                                currentPage: page,
                                pageSize: pageSize,
                                totalPages: totalPages,
                                totalRecords: totalRecords,
                                data: pairsList
                            });
                        }
                    });
                }
            });
        });
    }
}

module.exports = { PairsTable };
